﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.Odbc;
using System.Data.OleDb;


namespace GMAC.ReportingSystem.DataAccess
{
    class Connection
    {
        private string connectionString;
        public Connection(string ConnectionString)
        {
            connectionString = ConnectionString;
        }

        public DataSet ExecuteMultipleSelect(string sSQL)
        {
            DataSet ds = new DataSet();
            OleDbConnection con = new OleDbConnection(connectionString);
            try
            {
                // con.ConnectionTimeout = 0;
                OleDbCommand command = new OleDbCommand();
                command.Connection = con;
                command.CommandText = sSQL;
                command.CommandType = CommandType.Text;
                OleDbDataAdapter adapter = new OleDbDataAdapter(command);
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw;
            }
            finally {
                con.Close();
                con = null;
            }
        }

        public DataTable  ExecuteReader(String sSQL, int reconect) {

            OleDbConnection con = new OleDbConnection(connectionString);
            OleDbDataReader reader;
            try
            {
                if (reconect >= 0)
                {
                    DataSet ds = new DataSet(); 
                    OleDbCommand command = new OleDbCommand();
                    command.CommandText = sSQL;
                    command.CommandType = CommandType.Text;
                    command.Connection = con;
                    con.Open();
                    using ( reader = command.ExecuteReader())
                    {
                        ds = new DataSet();
                        ds = ConvertDataReaderToDataSet(reader);
                        reader.Close();
                    }
                    return ds.Tables[0];  
                }
                else
                    throw new TimeoutException(); 
            }
            catch
            {
                if (reconect == 0)
                     throw ;
                else
                {
                con.Close();
                return ExecuteReader(sSQL, reconect - 1);
                }
            }
            finally {
                reader = null; 
                con.Close();
            }
          

        }

        public DataTable ExecuteSelect(string sSQL)
        {
            DataSet ds = ExecuteMultipleSelect(sSQL);
            if (ds.Tables != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            return null;
        }

     
        public object ExecuteOperation(string sSQL)
        {
            OleDbConnection con = new OleDbConnection(connectionString);
            try
            {
                //con.ConnectionTimeout = 0;
                OleDbCommand command = new OleDbCommand();
                command.Connection = con;
                command.CommandText = sSQL;
                return command.ExecuteScalar();
            }
            catch
            {

                throw;
            }
            finally {
                con.Close();
                con = null;
            } 
        
        }
        public int ExecuteNonQuery(string sSQL)
        {
            OleDbConnection conLocal = new OleDbConnection(connectionString);
            try
            {
                //con.ConnectionTimeout = 0;
                OleDbCommand command = new OleDbCommand();
                command.Connection = conLocal;
                command.CommandText = sSQL;
                conLocal.Open();
                return command.ExecuteNonQuery();
            }
            catch(Exception ex)
            {

                throw;
            }
            finally
            {
                conLocal.Close();
                conLocal = null;
            }
            
        }

        public static DataSet ConvertDataReaderToDataSet(OleDbDataReader reader)
        {
            DataSet dataSet = new DataSet();
            do
            {
                // Create new data table

                DataTable schemaTable = reader.GetSchemaTable();
                DataTable dataTable = new DataTable();

                if (schemaTable != null)
                {
                    // A query returning records was executed

                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        DataRow dataRow = schemaTable.Rows[i];
                        // Create a column name that is unique in the data table
                        string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>";
                        // Add the column definition to the data table
                        DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
                        dataTable.Columns.Add(column);
                    }

                    dataSet.Tables.Add(dataTable);

                    // Fill the data table we just created

                    while (reader.Read())
                    {
                        DataRow dataRow = dataTable.NewRow();

                        for (int i = 0; i < reader.FieldCount; i++)
                            dataRow[i] = reader.GetValue(i);

                        dataTable.Rows.Add(dataRow);
                    }
                }
                else
                {
                    // No records were returned

                    DataColumn column = new DataColumn("RowsAffected");
                    dataTable.Columns.Add(column);
                    dataSet.Tables.Add(dataTable);
                    DataRow dataRow = dataTable.NewRow();
                    dataRow[0] = reader.RecordsAffected;
                    dataTable.Rows.Add(dataRow);
                }
            }
            while (reader.NextResult());
            return dataSet;
        }
    }
}
